Skip Headers

Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 10 Full-Text Search Over XML

This chapter describes full-text search over XML using Oracle. It explains how to use SQL function `contains` and XPath function `ora:contains`, the two functions used by Oracle Database to do full-text search over XML data.

See Also: Oracle Text Reference and Oracle Text Application Developer's Guide for more information on Oracle Text

This chapter contains these topics:

- Overview of Full-Text Search for XML

- About the Full-Text Search Examples

- Overview of CONTAINS and ora:contains

- CONTAINS SQL Function

- ora:contains XPath Function

- Text Path BNF Specification

- Support for Full-Text XML Examples

## Overview of Full-Text Search for XML

Oracle supports full-text search on documents that are managed by the Oracle Database. If your documents are XML, then you can use the XML structure of the document to restrict the full-text search. For example, you may want to find all purchase orders that contain the word "electric" using full-text search. If the purchase orders are in XML form, then you can restrict the search by finding all purchase orders that contain the word "electric" in a comment, or by finding all purchase orders that contain the word "electric" in a comment under line items. If your XML documents are of type `XMLType`, then you can project the results of your query using the XML structure of the document. For example, after finding all purchase orders that contain the word "electric" in a comment, you may want to return just the comments, or just the comments that contain the word "electric".

### Comparison of Full-Text Search and Other Search Types

Full-text search differs from structured search or substring search in the following ways:

- A full-text search looks for whole words rather than substrings. A substring search for comments that contain the string "law" might return a comment that contains "my lawn is going wild". A full-text search for the word "law" will not.

- A full-text search will support some language-based and word-based searches which substring searches cannot. You can use a language-based search, for example, to find all the comments that contain a word with the same linguistic stem as "mouse", and Oracle Text will find "mouse" and "mice". You can use a word-based search, for example, to find all the comments that contain the word "lawn" within 5 words of "wild".

- A full-text search generally involves some notion of relevance. When you do a full-text search for all the comments that contain the word "lawn", for example, some results are more relevant than others. Relevance is often related to the number of times the search word (or similar words) occur in the document.

### XML search

XML search is different from unstructured document search. In unstructured document search you generally search across a set of documents to return the documents that satisfy your text predicate. In XML search you often want to use the structure of the XML document to restrict the search. And you often want to return just the part of the document that satisfies the search.

### Search using Full-Text and XML Structure

There are two ways to do a search that includes full-text search and XML structure:

- Include the structure inside the full-text predicate, using SQL function `contains`:

      ... WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)')
                > 0 ...

  Function `contains` is an extension to SQL, and can be used in any query. It requires a `CONTEXT` full-text index.

- Include the full-text predicate inside the structure, using the `ora:contains` XPath function:

      ... '/purchaseOrder/items/item/comment[ora:contains(text(), "electric")>0]' ...

  The `ora:contains` XPath function is an extension to XPath, and can be used in any call to `existsNode`, `extract`, or `extractValue`.

## About the Full-Text Search Examples

This section describes details about the examples included in this chapter.

### Roles and Privileges

To run the examples you will need the `CTXAPP` role, as well as `CONNECT` and `RESOURCE`. You must also have `EXECUTE` privilege on the `CTXSYS` package `CTX_DDL`.

### Schema and Data for Full-Text Search Examples

Examples in this chapter are based on "The Purchase Order Schema", W3C XML Schema Part 0: Primer.

See Also: `http://www.w3.org/TR/xmlschema-0/#POSchema`

The data in the examples is "Purchase-Order XML Document, po001.xml". Some of the performance examples are based on a larger table (`PURCHASE_ORDERS_xmltype_big`), which is included in the downloadable version only.

See Also: `http://www.w3.org/TR/xmlschema-0/#po.xml`

Some examples here use datatype `VARCHAR2`; others use `XMLType`. All examples that use `VARCHAR2` will also work with `XMLType`.

## Overview of CONTAINS and ora:contains

This section contains these topics:

- Overview of SQL Function CONTAINS

- Overview of XPath Function ora:contains

- Comparison of CONTAINS and ora:contains

### Overview of SQL Function CONTAINS

SQL function `contains` returns a positive number for rows where `[schema.]column` matches `text_query`, and zero otherwise. It is a user-defined function, a standard extension method in SQL. It requires an index of type `CONTEXT`. If there is no `CONTEXT` index on the column being searched, then `contains` throws an error.

Syntax

    contains([schema.]column, text_query VARCHAR2 [,label NUMBER])
    RETURN NUMBER

Example 10-1 Simple CONTAINS Query

A typical query looks like this:

    SELECT id FROM purchase_orders WHERE contains(doc, 'lawn') > 0;

This query uses table `purchase_orders` and index `po_index`. It returns the ID for each row in table `purchase_orders` where the `doc` column contains the word "`lawn`".

Example 10-2 CONTAINS with a Structured Predicate

SQL function `contains` can be used in any SQL query. Here is an example using table `purchase_orders` and index `po_index`:

    SELECT id FROM purchase_orders WHERE contains(doc, 'lawn') > 0 AND id < 25;

Example 10-3 CONTAINS Using XML Structure to Restrict the Query

Suppose `doc` is a column that contains a set of XML documents. You can do full-text search over `doc`, using its XML structure to restrict the query. This query uses table `purchase_orders` and index `po_index-path-section`:

    SELECT id FROM purchase_orders WHERE contains(doc, 'lawn WITHIN comment') > 0;

Example 10-4 CONTAINS with Structure Inside Full-Text Predicate

More complex structure restrictions can be applied with the `INPATH` operator and an XPath expression. This query uses table `purchase_orders` and index `po_index-path-section`:

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

### Overview of XPath Function ora:contains

XPath function `ora:contains` can be used in an XPath expression inside an XQuery expression or in a call to SQL function `existsNode`, `extract`, or `extractValue`. It is used to restrict a structural search with a full-text predicate. Function `ora:contains` returns a positive integer when the `input_text` matches `text_query` (the higher the number, the more relevant the match), and zero otherwise. When used in an XQuery expression, the XQuery return type is `xs:integer()`; when used in an XPath expression outside of an XQuery expression, the XPath return type is `number`.

Argument `input_text` must evaluate to a single text node or an attribute. The syntax and semantics of `text_query` in `ora:contains` are the same as `text_query` in `contains`, with the following restrictions:

- Argument `text_query` cannot include any structure operators (`WITHIN`, `INPATH`, or `HASPATH`).

- If the `weight` score-weighting operator is used, the weights will be ignored.

Function `ora:contains` extends XPath through a standard mechanism: it is a user-defined function in the Oracle XML DB namespace, `ora`.

Syntax

    ora:contains(input_text NODE*, text_query STRING
                 [,policy_name STRING]
                 [,policy_owner STRING])

Example 10-5 shows a call to `ora:contains` in the XPath parameter to `existsNode`. Note that the third parameter to `existsNode` (the Oracle XML DB namespace, `ora`) is required. This example uses table `purchase_orders_xmltype`.

Example 10-5 ora:contains with an Arbitrarily Complex Text Query

    SELECT id
      FROM purchase_orders_xmltype
      WHERE
        existsNode(doc,
                   '/purchaseOrder/comment
                      [ora:contains(text(), "($lawns AND wild) OR flamingo") > 0]',
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

See Also: "ora:contains XPath Function" for more on the `ora:contains` XPath function

### Comparison of CONTAINS and ora:contains

SQL function `contains`:

- Needs a `CONTEXT` index to run. If there is no index, then you get an error.

- Does an indexed search and is generally very fast

- Returns a score (through SQL function `score`)

- Can restrict a search using both full text and XML structure

- Restricts a search based on documents (rows in a table) rather than nodes

- Cannot be used for XML structure-based projection (pulling out parts of an XML document)

XPath function `ora:contains`:

- Does not need an index to run, so it is very flexible

- Separates application logic from storing and indexing considerations

- Might do an unindexed search, so it might be resource-intensive

- Does not return a score

- Can restrict a search using full text in an XPath expression

- Can be used for XML structure-based projection (pulling out parts of an XML document)

Use `contains` when you want a fast, index-based, full-text search over XML documents, possibly with simple XML structure constraints. Use `ora:contains` when you need the flexibility of full-text search in XPath (possibly without an index), or when you need to do projection, and you do not need a score.

## CONTAINS SQL Function

This section contains these topics:

- Full-Text Search Using Function CONTAINS

- SCORE SQL Function

- Structure: Restricting the Scope of a CONTAINS Search

- Structure: Projecting the CONTAINS Result

- Indexing with the CONTEXT Index

### Full-Text Search Using Function CONTAINS

The second argument to SQL function `contains`, `text_query`, is a string that specifies the full-text search. `text_query` has its own language, based on the SQL/MM Full-Text standard.

See Also: ISO/IEC 13249-2:2000, Information technology - Database languages - SQL Multimedia and Application Packages - Part 2: Full-Text, International Organization For Standardization, 2000 Oracle Text Reference for more information on the operators in the `text_query` language

The examples in the rest of this section show some of the power of full-text search. They use just a few of the available operators: Booleans (`AND`, `OR`, `NOT`) and stemming. The example queries search over a `VARCHAR2` column (`PURCHASE_ORDERS.doc`) with a text index (indextype `CTXSYS.CONTEXT`).

#### Boolean Operators: AND, OR, NOT

The `text_query` language supports arbitrary combinations of `AND`, `OR`, and `NOT`. Precedence can be controlled using parentheses. The Boolean operators can be written in any of the following ways:

- `AND`, `OR`, `NOT`

- `and`, `or`, `not`

- `&`, `|`, `~`

Note that `NOT` is a binary, not a unary operator here. The expression `alpha NOT(beta)` is equivalent to `alpha AND` unary-not`(beta)`, where unary-not stands for unary negation.

See Also: Oracle Text Reference for complete information on the operators you can use in `contains` and `ora:contains`

Example 10-6 CONTAINS Query with Simple Boolean

    SELECT id FROM purchase_orders WHERE contains(doc, 'lawn AND wild') > 0;

This example uses table `purchase_orders` and index `po_index`.

Example 10-7 CONTAINS Query with Complex Boolean

    SELECT id FROM purchase_orders
      WHERE contains(doc, '((lawn OR garden) AND (wild OR flooded)) NOT(flamingo)')
            > 0;

This example uses table `purchase_orders` and index `po_index`.

#### Stemming: $

The `text_query` language supports stemmed search. Example 10-8 returns all documents that contain some word with the same linguistic stem as "`lawns`", so it will find "`lawn`" or "`lawns`". The stem operator is written as a dollar sign (`$`). There is no operator `STEM` or `stem`.

Example 10-8 CONTAINS Query with Stemming

    SELECT id FROM purchase_orders WHERE contains(doc, '$(lawns)') > 0;

This example uses table `purchase_orders` and index `po_index`.

#### Combining Boolean and Stemming Operators

operators in the `text_query` language can be arbitrarily combined, as shown in Example 10-9.

Example 10-9 CONTAINS Query with Complex Query Expression

    SELECT id FROM purchase_orders
      WHERE contains(doc, '($lawns AND wild) OR flamingo') > 0;

This example uses table `purchase_orders` and index `po_index`.

See Also: Oracle Text Reference for a full list of text_query operators

### SCORE SQL Function

SQL function `contains` has a related function, `score`, which can be used anywhere in the query. It is a measure of relevance, and it is especially useful when doing full-text searches across large document sets. `score` is typically returned as part of the query result, used in the `ORDER BY` clause, or both.

Syntax

    score(label NUMBER) RETURN NUMBER

In Example 10-10, `score(10)` returns the score for each row in the result set. SQL function `score` returns the relevance of a row in the result set with respect to a particular call to function `contains`. A call to `score` is linked to a call to `contains` by a `LABEL` (in this case the number 10).

Example 10-10 Simple CONTAINS Query with SCORE

    SELECT score(10), id FROM purchase_orders
      WHERE contains(doc, 'lawn', 10) > 0 AND score(10) > 2
      ORDER BY score(10) DESC;

This example uses table `purchase_orders` and index `po_index`.

Function `score` always returns `0` if, for the corresponding `contains`, the `text_query` argument does not match the `input_text`, according to the matching rules dictated by the text index. If the `contains` `text_query` does match the `input_text`, then `score` will return a number greater than `0` and less than or equal to `100`. This number indicates the relevance of the `text_query` to the `input_text`. A higher number means a better match.

If the `contains` `text_query` consists of only the `HASPATH` operator and a Text Path, the score will be either `0` or `100`, because `HASPATH` tests for an exact match.

See Also: Oracle Text Reference for details on how the score is calculated

### Structure: Restricting the Scope of a CONTAINS Search

SQL function `contains` does a full-text search across the whole document by default. In our examples, a search for "lawn" with no structure restriction will find all purchase orders with the word "lawn" anywhere in them.

Oracle offers three ways to restrict `contains` queries using XML structure:

- `WITHIN`

- `INPATH`

- `HASPATH`

Note: For the purposes of this discussion, consider section to be the same as an XML node.

#### WITHIN Structure Operator

The `WITHIN` operator restricts a query to some section within an XML document. A search for purchase orders that contain the word "lawn" somewhere inside a comment section might use `WITHIN`. Section names are case-sensitive.

Example 10-11 WITHIN

    SELECT id FROM purchase_orders WHERE contains(DOC, 'lawn WITHIN comment') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

##### Nested WITHIN

You can restrict the query further by nesting `WITHIN`. Example 10-12 finds all documents that contain the word "`lawn`" within a section "`comment`", where that occurrence of "`lawn`" is also within a section "`item`".

Example 10-12 Nested WITHIN

    SELECT id FROM purchase_orders
      WHERE contains(doc, '(lawn WITHIN comment) WITHIN item') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

Example 10-12 returns no rows. Our sample purchase order does contain the word "`lawn`" within a comment. But the only comment within an item is "`Confirm this is electric`". So the nested `WITHIN` query will return no rows.

##### WITHIN Attributes

You can also search within attributes. Example 10-13 finds all purchase orders that contain the word `10` in the `orderDate` attribute of a `purchaseOrder` element.

Example 10-13 WITHIN an Attribute

    SELECT id FROM purchase_orders
      WHERE contains(doc, '10 WITHIN purchaseOrder@orderDate') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

By default, the minus sign ("`-`") is treated as a word separator: "`1999-10-20`" is treated as the three words "`1999`", "`10`" and "`20`". So this query returns one row.

Text in an attribute is not a part of the main searchable document. If you search for `10` without qualifying the `text_query` with `WITHIN purchaseOrder@orderDate`, then you will get no rows.

You cannot search attributes in a nested `WITHIN`.

##### WITHIN and AND

Suppose you want to find purchase orders that contain two words within a comment section: "lawn" and "electric". There can be more than one comment section in a `purchaseOrder`. So there are two ways to write this query, with two distinct results.

If you want to find purchase orders that contain both words, where each word occurs in some comment section, you would write a query like Example 10-14.

Example 10-14 WITHIN and AND: Two Words in Some Comment Section

    SELECT id FROM purchase_orders
      WHERE contains(doc, '(lawn WITHIN comment) AND (electric WITHIN comment)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

If you run this query against the `purchaseOrder` data, then it returns 1 row. Note that the parentheses are not needed in this example, but they make the query more readable.

If you want to find purchase orders that contain both words, where both words occur in the same comment, you would write a query like Example 10-15.

Example 10-15 WITHIN and AND: Two Words in the Same Comment

    SELECT id FROM purchase_orders
      WHERE contains(doc, '(lawn AND electric) WITHIN comment') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

Example 10-15 will return no rows. Example 10-16, which omits the parentheses around `lawn AND electric`, on the other hand, will return one row.

Example 10-16 WITHIN and AND: No Parentheses

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'lawn AND electric WITHIN comment') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

Operator `WITHIN` has a higher precedence than `AND`, so Example 10-16 is parsed as Example 10-17.

Example 10-17 WITHIN and AND: Parentheses Illustrating Operator Precedence

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'lawn AND (electric WITHIN comment)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

##### Definition of Section

The preceding examples have used the `WITHIN` operator to search within a section. A section can be a:

- path or zone section

  This is a concatenation, in document order, of all text nodes that are descendants of a node, with whitespace separating the text nodes. To convert from a node to a zone section, you must serialize the node and replace all tags with whitespace. path sections have the same scope and behavior as zone sections, except that path sections support queries with `INPATH` and `HASPATH` structure operators.

- field section

  This is the same as a zone section, except that repeating nodes in a document are concatenated into a single section, with whitespace as a separator.

- attribute section

- special section (sentence or paragraph)

  See Also: Oracle Text Reference for more information on special sections

#### INPATH Structure Operator

Operator `WITHIN` provides an easy and intuitive way to express simple structure restrictions in the `text_query`. For queries that use abundant XML structure, you can use operator `INPATH` plus a text path instead of nested `WITHIN` operators.

Operator `INPATH` takes a `text_query` on the left and a Text Path, enclosed in parentheses, on the right. Example 10-18 finds `purchaseOrders` that contain the word "electric" in the path `/purchaseOrder/items/item/comment`.

Example 10-18 Structure Inside Full-Text Predicate: INPATH

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

The scope of the search is the section indicated by the Text Path. If you choose a broader path, such as `/purchaseOrder/items`, you will still get 1 row returned, as shown in Example 10-19.

Example 10-19 Structure Inside Full-Text Predicate: INPATH

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'electric INPATH (/purchaseOrder/items)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

##### Text Path

The syntax and semantics of the Text Path are based on the w3c XPath 1.0 recommendation. Simple path expressions are supported (abbreviated syntax only), but functions are not. The following examples are meant to give the general flavor.

See Also: `http://www.w3.org/TR/xpath` for information on the W3C XPath 1.0 recommendation "Text Path BNF Specification" for the Text Path grammar

Example 10-20 finds all purchase orders that contain the word "`electric`" in a "`comment`" which is the direct child of an "`item`" with an attribute `partNum` equal to "`872-AA`", which in turn is the direct child of an "`items`", which is any number of levels down from the root node.

Example 10-20 INPATH with Complex Path Expression (1)

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'electric INPATH (//items/item[@partNum="872-AA"]/comment)')
            > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

Example 10-21 finds all purchase orders that contain the word "`lawnmower`" in a third-level "`item`" (or any of its descendants) that has a "`comment`" descendant at any level. This query returns one row. Note that the scope of the query is not a "`comment`", but the set of "`items`" that each have a "`comment`" as a descendant.

Example 10-21 INPATH with Complex Path Expression (2)

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'lawnmower INPATH (/*/*/item[.//comment])') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

##### Text Path Compared to XPath

The Text Path language differs from the XPath language in the following ways:

- Not all XPath operators are included in the Text Path language.

- XPath built-in functions are not included in the Text Path language.

- Text Path language operators are case-insensitive.

- If you use "`=`" inside a filter (inside square brackets), matching follows text-matching rules.

  Rules for case-sensitivity, normalization, stopwords and whitespace depend on the text index definition. To emphasize this difference, this kind of equality is referred to here as text-equals.

- Namespace support is not included in the Text Path language.

  The name of an element, including a namespace prefix if it exists, is treated as a string. So two namespace prefixes that map to the same namespace URI will not be treated as equivalent in the Text Path language.

- In a Text Path, the context is always the root node of the document.

  So in the purchase-order data, `purchaseOrder/items/item`, `/purchaseOrder/items/item`, and `./purchaseOrder/items/item` are all equivalent.

- If you want to search within an attribute value, then the direct parent of the attribute must be specified (wildcards cannot be used).

- A Text Path may not end in a wildcard (`*`).

See Also: "Text Path BNF Specification" for the Text Path grammar

##### Nested INPATH

You can nest `INPATH` expressions. The context for the Text Path is always the root node. It is not changed by a nested `INPATH`.

Example 10-22 finds purchase orders that contain the word "`electric`" in a "`comment`" section at any level, where the occurrence of that word is also in an "`items`" section that is the direct child of the top-level `purchaseOrder`.

Example 10-22 Nested INPATH

    SELECT id FROM purchase_orders
      WHERE contains(doc,
                     '(electric INPATH (//comment)) INPATH (/purchaseOrder/items)')
            > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

This nested `INPATH` query could be written more concisely as shown in Example 10-23.

Example 10-23 Nested INPATH Rewritten

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'electric INPATH (/purchaseOrder/items//comment)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

#### HASPATH Structure Operator

Operator `HASPATH` takes only one operand: a Text Path, enclosed in parentheses, on the right. Use `HASPATH` when you want to find documents that contain a particular section in a particular path, possibly with an "`=`" predicate. Note that this is a path search rather than a full-text search. You can check for existence of a section, or you can match the contents of a section, but you cannot do word searches. If your data is of type `XMLType`, then consider using SQL function `existsNode` instead of structure operator `HASPATH`.

Example 10-24 finds `purchaseOrders` that have some item that has a `USPrice`.

Example 10-24 Simple HASPATH

    SELECT id FROM purchase_orders
      WHERE contains(DOC, 'HASPATH (/purchaseOrder//item/USPrice)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

Example 10-25 finds `purchaseOrders` that have some item that has a `USPrice` that text-equals "`148.95`".

See Also: "Text Path Compared to XPath" for an explanation of text-equals

Example 10-25 HASPATH Equality

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'HASPATH (/purchaseOrder//item/USPrice="148.95")') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

`HASPATH` can be combined with other `contains` operators such as `INPATH`. Example 10-26 finds `purchaseOrders` that contain the word `electric` anywhere in the document and have some `item` that has a `USPrice` that text-equals `148.95` and contain `10` in the `purchaseOrder` attribute `orderDate`.

Example 10-26 HASPATH with Other Operators

    SELECT id FROM purchase_orders
      WHERE contains(doc,
                     'electric
                      AND HASPATH (/purchaseOrder//item/USPrice="148.95")
                      AND 10 INPATH (/purchaseOrder/@orderDate)')
            > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

### Structure: Projecting the CONTAINS Result

The result of a SQL query with a `contains` expression in the `WHERE` clause is always a set of rows (and possibly `score` information), or a projection over the rows that match the query. If you want to return only a part of each XML document that satisfies the `contains` expression, then use SQL functions `extract` and `extractValue`. Note that `extract` and `extractValue` operate on `XMLType`, so the following examples use the table `purchase_orders_xmltype`.

Example 10-27 finds `purchaseOrder`s that contain the word "electric" in a "comment" that is a descendant of the top-level `purchaseOrder`. Instead of returning the ID of the row for each result, `extract` is used to return only the "comment".

Example 10-27 Using EXTRACT to Scope the Results of a CONTAINS Query

    SELECT extract(doc,
                   '/purchaseOrder//comment',
                   'xmlns:ora="http://xmlns.oracle.com/xdb"') "Item Comment"
      FROM purchase_orders_xmltype
      WHERE contains(doc, 'electric INPATH (/purchaseOrder//comment)') > 0;

This example uses table `purchase_orders_xmltype` and index `po_index_xmltype`.

Note that the result of Example 10-27 is two instances of "`comment`". Function `contains` indicates which rows contain the word "`electric`" in a "`comment`" (the row with `ID`=1), and `extract` extracts all the instances of "`comment`" in the document at that row. There are two instances of "`comment`" in our `purchaseOrder`, and the query returns both of them.

This might not be what you want. If you want the query to return only the instances of "`comment`" that satisfy the `contains` expression, then you must repeat that predicate in the `extract`. You do that with `ora:contains`, which is an XPath function.

Example 10-28 returns only the "comment" that matches the `contains` expression.

Example 10-28 Using EXTRACT and ora:contains to Project the Result of a CONTAINS Query

    SELECT
      extract(doc,
              '/purchaseOrder/items/item/comment
                [ora:contains(text(), "electric") > 0]',
              'xmlns:ora="http://xmlns.oracle.com/xdb"') "Item Comment"
      FROM purchase_orders_xmltype
      WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

### Indexing with the CONTEXT Index

This section contains these topics:

- Introduction to the CONTEXT Index

- Effect of the CONTEXT Index on CONTAINS

- CONTEXT Index: Preferences

- Introduction to Section Groups

#### Introduction to the CONTEXT Index

The Oracle general purpose full-text indextype is the `CONTEXT` indextype, owned by the database user `CTXSYS`. To create a default full-text index, use the regular SQL `CREATE INDEX` command, and add the clause `INDEXTYPE IS CTXSYS.CONTEXT`, as shown in Example 10-29.

Example 10-29 Simple CONTEXT Index on Table PURCHASE_ORDERS

    CREATE INDEX po_index ON purchase_orders(doc)
      INDEXTYPE IS CTXSYS.CONTEXT ;

This example uses table `PURCHASE_ORDERS`.

You have many choices available when building a full-text index. These choices are expressed as indexing preferences. To use an indexing preference, add the `PARAMETERS` clause to `CREATE INDEX`, as shown in Example 10-30.

See Also: "CONTEXT Index: Preferences"

Example 10-30 Simple CONTEXT Index on Table PURCHASE_ORDERS with Path Section Group

    CREATE INDEX po_index ON purchase_orders(doc)
      INDEXTYPE IS CTXSYS.CONTEXT
      PARAMETERS ('section group CTXSYS.PATH_SECTION_GROUP');

This example uses table `purchase_orders`.

Oracle Text provides other index types, such as `CTXCAT` and `CTXRULE`, which are outside the scope of this chapter.

See Also: Oracle Text Reference for more information on `CONTEXT` indexes

##### CONTEXT Index on XMLType Table

You can build a `CONTEXT` index on any data that contains text. Example 10-29 creates a `CONTEXT` index on a `VARCHAR2` column. The syntax to create a `CONTEXT` index on a column of type `CHAR`, `VARCHAR`, `VARCHAR2`, `BLOB`, `CLOB`, `BFILE`, `XMLType`, or `URIType` is the same. Example 10-31 creates a `CONTEXT` index on a column of type `XMLType`.

Example 10-31 Simple CONTEXT Index on Table PURCHASE_ORDERS_xmltype

    CREATE INDEX po_index_xmltype ON purchase_orders_xmltype(doc)
      INDEXTYPE IS CTXSYS.CONTEXT;

This example uses table `purchase_orders_xmltype`. The section group defaults to `PATH_SECTION_GROUP`.

If you have a table of type `XMLType`, then you need to use object syntax to create the `CONTEXT` index as shown in Example 10-32.

Example 10-32 Simple CONTEXT Index on XMLType Table

    CREATE INDEX po_index_xmltype_table
      ON purchase_orders_xmltype_table (OBJECT_VALUE)
      INDEXTYPE IS CTXSYS.CONTEXT;

This example uses table `purchase_orders_xmltype`.

You can query the table using the syntax in Example 10-33.

Example 10-33 CONTAINS Query on XMLType Table

    SELECT extract(OBJECT_VALUE, '/purchaseOrder/@orderDate') "Order Date"
      FROM purchase_orders_xmltype_table
      WHERE contains(OBJECT_VALUE, 'electric INPATH (/purchaseOrder//comment)') > 0;

This example uses table `purchase_orders_xmltype_table` and index `po_index_xmltype_table`.

##### Maintaining the CONTEXT Index

The `CONTEXT` index, like most full-text indexes, is asynchronous. When indexed data is changed, the `CONTEXT` index might not change until you take some action, such as calling a procedure to synchronize the index. There are a number of ways to manage changes to the `CONTEXT` index, including some options that are new for this release.

The `CONTEXT` index can get fragmented over time. A fragmented index uses more space, and it leads to slower queries. There are a number of ways to optimize (defragment) the `CONTEXT` index, including some options that are new for this release.

See Also: Oracle Text Reference for more information on `CONTEXT` index maintenance

##### Roles and Privileges

You do not need any special privileges to create a `CONTEXT` index. You need the `CTXAPP` role to create and delete preferences and to use the Oracle Text PL/SQL packages. You must also have `EXECUTE` privilege on the `CTXSYS` package `CTX_DDL`.

#### Effect of the CONTEXT Index on CONTAINS

You must create an index of type `CONTEXT` in order to use SQL function `contains`. If you call `contains`, and the column given in the first argument does not have an index of type `CONTEXT`, then an error is raised.

The syntax and semantics of `text_query` depend on the choices you make when you build the `CONTEXT` index. For example:

- What counts as a word?

- Are very common words processed?

- What is a common word?

- Is the text search case-sensitive?

- Can the text search include themes (concepts) as well as keywords?

#### CONTEXT Index: Preferences

A preference can be considered a collection of indexing choices. Preferences include section group, datastore, filter, wordlist, stoplist and storage. This section shows how to set up a lexer preference to make searches case-sensitive.

You can use procedure `CTX_DDL.create_preference` (or `CTX_DDL.create_stoplist`) to create a preference. Override default choices in that preference group by setting attributes of the new preference, using procedure `CTX_DDL.set_attribute` . Then use the preference in a `CONTEXT` index by including `preference type preference_name` in the `PARAMETERS` string of `CREATE INDEX`.

Once a preference has been created, you can use it to build any number of indexes.

##### Making Search Case-Sensitive

Full-text searches with `contains` are case-insensitive by default. That is, when matching words in `text_query` against words in the document, case is not considered. Section names and attribute names, however, are always case-sensitive.

If you want full-text searches to be case-sensitive, then you need to make that choice when building the `CONTEXT` index. Example 10-34 returns 1 row, because "`HURRY`" in `text_query` matches "`Hurry`" in the `purchaseOrder` with the default case-insensitive index.

Example 10-34 CONTAINS: Default Case Matching

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'HURRY INPATH (/purchaseOrder/comment)') > 0;

This example uses table `purchase_orders` and index `po_index-path-section`.

Example 10-35 creates a new lexer preference `my_lexer`, with the attribute `mixed_case` set to `TRUE`. It also sets printjoin characters to "`-`" and "`!`" and "`,`". You can use the same preferences for building `CONTEXT` indexes and for building policies.

See Also: Oracle Text Reference for a full list of lexer attributes

Example 10-35 Create a Preference for Mixed Case

    BEGIN
      CTX_DDL.create_preference(PREFERENCE_NAME  =>  'my_lexer',
                                OBJECT_NAME      =>  'BASIC_LEXER');

      CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer',
                            ATTRIBUTE_NAME   =>  'mixed_case',
                            ATTRIBUTE_VALUE  =>  'TRUE');

      CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer',
                            ATTRIBUTE_NAME   =>  'printjoins',
                            ATTRIBUTE_VALUE  =>  '-,!');
    END ;
    /

Example 10-36 builds a `CONTEXT` index using the new `my_lexer` lexer preference.

Example 10-36 CONTEXT Index on PURCHASE_ORDERS Table, Mixed Case

    CREATE INDEX po_index ON purchase_orders(doc)
      INDEXTYPE IS CTXSYS.CONTEXT
      PARAMETERS('lexer my_lexer section group CTXSYS.PATH_SECTION_GROUP');

This example uses table `purchase_orders` and preference `preference-case-mixed`.

Example 10-34 returns no rows, because "`HURRY`" in `text_query` no longer matches "`Hurry`" in the `purchaseOrder`. Example 10-37 returns one row, because the `text_query` term "`Hurry`" exactly matches the word "`Hurry`" in the `purchaseOrder`.

Example 10-37 CONTAINS: Mixed (Exact) Case Matching

    SELECT id FROM purchase_orders
      WHERE contains(doc, 'Hurry INPATH (/purchaseOrder/comment)') > 0;

This example uses table `purchase_orders` and index `po_index-case-mixed`.

#### Introduction to Section Groups

One of the choices you make when creating a `CONTEXT` index is section group. A section group instance is based on a section group type. The section group type specifies the kind of structure in your documents, and how to index (and therefore search) that structure. The section group instance may specify which structure elements are indexed. Most users will either take the default section group or use a pre-defined section group.

##### Choosing a Section Group Type

The section group types useful in XML searching are:

- `PATH_SECTION_GROUP`

  Choose this when you want to use `WITHIN`, `INPATH` and `HASPATH` in queries, and you want to be able to consider all sections to scope the query.

- `XML_SECTION_GROUP`

  Choose this when you want to use `WITHIN`, but not `INPATH` and `HASPATH`, in queries, and you want to be able to consider only explicitly-defined sections to scope the query. `XML_SECTION_GROUP` section group type supports `FIELD` sections in addition to `ZONE` sections. In some cases `FIELD` sections offer significantly better query performance.

- `AUTO_SECTION_GROUP`

  Choose this when you want to use `WITHIN`, but not `INPATH` and `HASPATH`, in queries, and you want to be able to consider most sections to scope the query. By default all sections are indexed (available for query restriction). You can specify that some sections are not indexed (by defining `STOP` sections).

- `NULL_SECTION_GROUP`

  Choose this when defining no XML sections.

Other section group types include:

- `BASIC_SECTION_GROUP`

- `HTML_SECTION_GROUP`

- `NEWS_SECTION_GROUP`

Oracle recommends that most users with XML full-text search requirements use `PATH_SECTION_GROUP`. Some users may prefer `XML_SECTION_GROUP` with `FIELD` sections. This choice will generally give better query performance and a smaller index, but it is limited to documents with fielded structure (searchable nodes are all leaf nodes that do not repeat).

See Also: Oracle Text Reference for a detailed description of the `XML_SECTION_GROUP` section group type

##### Choosing a Section Group

When choosing a section group to use with your index, you can choose a supplied section group, take the default, or create a new section group based on the section group type you have chosen.

There are supplied section groups for section group types `PATH_SECTION_GROUP`, `AUTO_SECTION_GROUP`, and `NULL_SECTION_GROUP`. The supplied section groups are owned by `CTXSYS` and have the same name as their section group types. For example, the supplied section group of section group type `PATH_SECTION_GROUP` is `CTXSYS.PATH_SECTION_GROUP`.

There is no supplied section group for section group type `XML_SECTION_GROUP`, because a default `XML_SECTION_GROUP` would be empty and therefore meaningless. If you want to use section group type `XML_SECTION_GROUP`, then you must create a new section group and specify each node that you want to include as a section.

When you create a `CONTEXT` index on data of type `XMLType`, the default section group is the supplied section group `CTXSYS.PATH_SECTION_GROUP`. If the data is `VARCHAR` or `CLOB`, then the default section group is `CTXSYS.NULL_SECTION_GROUP`.

See Also: Oracle Text Reference for instructions on creating your own section group

To associate a section group with an index, add `section group <section group name>` to the `PARAMETERS` string, as in Example 10-38.

Example 10-38 Simple CONTEXT Index on purchase_orders Table with Path Section Group

    CREATE INDEX po_index ON purchase_orders(doc)
      INDEXTYPE IS CTXSYS.CONTEXT
      PARAMETERS ('section group CTXSYS.PATH_SECTION_GROUP');

This example uses table `purchase_orders`.

## ora:contains XPath Function

Function `ora:contains` is an Oracle-defined XPath function for use in the XPath argument to the SQL/XML functions `existsNode`, `extract`, and `extractValue`.

Note: These functions are not yet a part of the SQL/XML standard, but these functions or very similar functions are expected to be part of a future version of SQL/XML.

The `ora:contains` function name consists of a name (`contains`) plus a namespace prefix (`ora:`). When you use `ora:contains` in `existsNode`, `extract` or `extractValue` you must also supply a namespace mapping parameter, `xmlns:ora="http://xmlns.oracle.com/xdb"`.

`ora:contains` returns a number; it does not return a score. It returns a positive number if the `text_query` matches the `input_text`. Otherwise it returns zero.

### Full-Text Search Using Function ora:contains

The `ora:contains` argument `text_query` is a string that specifies the full-text search. The `ora:contains` `text_query` is the same as the `contains` `text_query`, with the following restrictions:

- `ora:contains` `text_query` must not include any of the structure operators `WITHIN`, `INPATH`, or `HASPATH`

- `ora:contains` `text_query` may include the score weighting operator `weight(*)`, but weights will be ignored

If you include any of the following in the `ora:contains` `text_query`, the query cannot use a `CONTEXT` index:

- Score-based operator `MINUS` (`-`) or `threshold` (`>`)

- Selective, corpus-based expansion operator `FUZZY` (`?`) or `soundex` (`!`)

See Also: "XPath Rewrite and the CONTEXT Index"

Example 10-39 shows a full-text search using an arbitrary combination of Boolean operators and `$` (stemming).

Example 10-39 ora:contains with an Arbitrarily Complex Text Query

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment
                         [ora:contains(text(), "($lawns AND wild) OR flamingo") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

This example uses table `purchase_orders_xmltype`.

See Also: "Full-Text Search Using Function CONTAINS" for a description of full-text operators Oracle Text Reference for a full list of the operators you can use in `contains` and `ora:contains`

Matching rules are defined by the policy, `policy_owner``.``policy_name`. If `policy_owner` is absent, then the policy owner defaults to the current user. If both `policy_name` and `policy_owner` are absent, then the policy defaults to `CTXSYS.DEFAULT_POLICY_ORACONTAINS`.

### Structure: Restricting the Scope of an ora:contains Query

When you use `ora:contains` in an XPath expression, the scope is defined by argument `input_text`. This argument is evaluated in the current XPath context. If the result is a single text node or an attribute, then that node is the target of the `ora:contains` search. If `input_text` does not evaluate to a single text node or an attribute, an error is raised.

The policy determines the matching rules for `ora:contains`. The section group associated with the default policy for `ora:contains` is of type `NULL_SECTION_GROUP`.

`ora:contains` can be used anywhere in an XPath expression, and its `input_text` argument can be any XPath expression that evaluates to a single text node or an attribute.

### Structure: Projecting the ora:contains Result

If you want to return only a part of each XML document, then use `extract` to project a node sequence or `extractValue` to project the value of a node.

Example 10-40 ora:contains in EXISTSNODE and EXTRACT

This example returns the `orderDate` for each `purchaseOrder` that has a `comment` that contains the word "`lawn`". It uses table `purchase_orders_xmltype`.

    SELECT extract(doc, '/purchaseOrder/@orderDate') "Order date"
      FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment[ora:contains(text(), "lawn") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

Function `existsNode` restricts the result to rows (documents) where the `purchaseOrder` element includes some `comment` that contains the word "`lawn`". Function `extract` then returns the value of attribute `orderDate` from those `purchaseOrder` elements. If `//comment` had been extracted, then both comments from the sample document would have been returned, not just the comment that matches the `WHERE` clause.

See Also: Example 10-27, "Using EXTRACT to Scope the Results of a CONTAINS Query"

### Policies for ora:contains Queries

The `CONTEXT` index on a column determines the semantics of `contains` queries on that column. Because `ora:contains` does not rely on a supporting index, some other means must be found to provide many of the same choices when doing `ora:contains` queries. A policy is a collection of preferences that can be associated with an `ora:contains` query to give the same sort of semantic control as the indexing choices give to the `contains` user.

#### Introduction to Policies for ora:contains Queries

When using SQL function `contains`, indexing preferences affect the semantics of the query. You create a preference using procedure `CTX_DDL.create_preference` (or `CTX_DDL.create_stoplist`). You override default choices by setting attributes of the new preference, using procedure `CTX_DDL.set_attribute`. Then you use the preference in a `CONTEXT` index by including `preference_type preference_name` in the `PARAMETERS` string of `CREATE INDEX`.

See Also: "CONTEXT Index: Preferences"

Because `ora:contains` does not have a supporting index, a different mechanism is needed to apply preferences to a query. That mechanism is a policy, consisting of a collection of preferences, and it is used as a parameter to `ora:contains`.

##### Policy Example: Supplied Stoplist

Example 10-41 creates a policy with an empty stopwords list.

Example 10-41 Create a Policy to Use with ora:contains

    BEGIN
      CTX_DDL.create_policy(POLICY_NAME  =>  'my_nostopwords_policy',
                            STOPLIST     =>  'CTXSYS.EMPTY_STOPLIST');
    END;
    /

For simplicity, this policy consists of an empty stoplist, which is owned by user `CTXSYS`. You could create a new stoplist to include in this policy, or you could reuse a stoplist (or lexer) definition that you created for a `CONTEXT` index.

Refer to this policy in an `ora:contains` expression to search for all words, including the most common ones (stopwords). Example 10-42 returns zero comments, because "`is`" is a stopword by default and cannot be queried.

Example 10-42 Query on a Common Word with ora:contains

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment[ora:contains(text(), "is") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

This example uses table `purchase_orders_xmltype`.

Example 10-43 uses the policy created in Example 10-41 to specify an empty stopword list. This query finds "is" and returns 1 comment.

Example 10-43 Query on a Common Word with ora:contains and Policy my_nostopwords_policy

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment
                         [ora:contains(text(), "is", "MY_NOSTOPWORDS_POLICY") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

This example uses table `purchase_orders_xmltype` and policy `my_nostopwords_policy`. (This policy was implicitly named as all uppercase in Example 10-41. Because XPath is case-sensitive, it must be referred to in the XPath predicate using all uppercase: `MY_NOSTOPWORDS_POLICY`, not `my_nostopwords_policy`.)

Note: As always: SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes. XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase. For example, if you create a table named `my_table` in SQL without using double-quotes, then you must refer to it in XML as "`MY_TABLE`".

#### Effect of Policies on ora:contains

The `ora:contains` policy affects the matching semantics of `text_query`. The `ora:contains` policy may include a lexer, stoplist, wordlist preference, or any combination of these. Other preferences that can be used to build a `CONTEXT` index are not applicable to `ora:contains`. The effects of the preferences are as follows:

- The wordlist preference tweaks the semantics of the stem operator.

- The stoplist preference defines which words are too common to be indexed (searchable).

- The lexer preference defines how words are tokenized and matched. For example, it defines which characters count as part of a word and whether matching is case-sensitive.

See Also: "Policy Example: Supplied Stoplist" for an example of building a policy with a predefined stoplist "Policy Example: User-Defined Lexer" for an example of a case-sensitive policy

##### Policy Example: User-Defined Lexer

When you search for a document that contains a particular word, you usually want the search to be case-insensitive. If you do a search that is case-sensitive, then you will often miss some expected results. For example, if you search for `purchaseOrders` that contain the phrase "baby monitor", then you would not expect to miss our example document just because the phrase is written "Baby Monitor".

Full-text searches with `ora:contains` are case-insensitive by default. Section names and attribute names, however, are always case-sensitive.

If you want full-text searches to be case-sensitive, then you need to make that choice when you create a policy. You can use this procedure:

1. Create a preference using the procedure `CTX_DDL.create_preference` (or `CTX_DDL.create_stoplist`).

2. Override default choices in that preference object by setting attributes of the new preference, using procedure `CTX_DDL.set_attribute`.

3. Use the preference as a parameter to `CTX_DDL.create_policy`.

4. Use the policy name as the third argument to `ora:contains` in a query.

Once you have created a preference, you can reuse it in other policies or in `CONTEXT` index definitions. You can use any policy with any `ora:contains` query.

Example 10-44 returns 1 row, because "HURRY" in `text_query` matches "Hurry" in the `purchaseOrder` with the default case-insensitive index.

Example 10-44 ora:contains, Default Case-Sensitivity

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment[ora:contains(text(), "HURRY") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

This example uses table `purchase_orders_xmltype`.

Example 10-45 creates a new lexer preference `my_lexer`, with the attribute `mixed_case` set to `TRUE`. It also sets printjoin characters to "-" and "!" and ",". You can use the same preferences for building `CONTEXT` indexes and for building policies.

See Also: Oracle Text Reference for a full list of lexer attributes

Example 10-45 Create a Preference for Mixed Case

    BEGIN
      CTX_DDL.create_preference(PREFERENCE_NAME  =>  'my_lexer',
                                OBJECT_NAME      =>  'BASIC_LEXER');
      CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'MY_LEXER',
                            ATTRIBUTE_NAME   =>  'MIXED_CASE',
                            ATTRIBUTE_VALUE  =>  'TRUE');
      CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer',
                            ATTRIBUTE_NAME   =>  'printjoins',
                            ATTRIBUTE_VALUE  =>  '-,!');
    END ;
    /

Example 10-46 creates a new policy `my_policy` and specifies only the lexer. All other preferences are defaulted.

Example 10-46 Create a Policy with Mixed Case (Case-Insensitive)

    BEGIN
      CTX_DDL.create_policy(POLICY_NAME  => 'my_policy',
                            LEXER        => 'my_lexer');
    END ;
    /

This example uses preference-case-mixed.

Example 10-47 uses the new policy in a query. It returns no rows, because "`HURRY`" in `text_query` no longer matches "`Hurry`" in the `purchaseOrder`.

Example 10-47 ora:contains, Case-Sensitive (1)

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment
                         [ora:contains(text(), "HURRY", "my_policy") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

This example uses table `purchase_orders_xmltype`.

Example 10-48 returns one row, because the `text_query` term "`Hurry`" exactly matches the word "`Hurry`" in the `purchaseOrder`.

Example 10-48 ora:contains, Case-Sensitive (2)

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/comment
                         [ora:contains(text(), "is going wild") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

This example uses table `purchase_orders_xmltype`.

#### Policy Defaults

The policy argument to `ora:contains` is optional. If it is omitted, then the query uses the default policy `CTXSYS.DEFAULT_POLICY_ORACONTAINS`.

When you create a policy for use with `ora:contains`, you do not need to specify every preference. In Example 10-46, for example, only the lexer preference was specified. For the preferences that are not specified, `CREATE_POLICY` uses the default preferences:

- `CTXSYS.DEFAULT_LEXER`

- `CTXSYS.DEFAULT_STOPLIST`

- `CTXSYS.DEFAULT_ WORDLIST`

Creating a policy follows copy semantics for preferences and their attributes, just as creating a `CONTEXT` index follows copy semantics for index metadata.

### ora:contains Searches Over a Collection of Elements

It is common to use the schema annotation `storeVarrayAsTable="true"`, and set parameter `genTables="true"` during schema registration. This automatically creates the tables needed to store the corresponding XML data.

However, if you use these settings, then you will not be able to use Oracle Text indexes for text-based `ora:contains` searches over a collection of elements – that is, a document with repetitions of the same element type. The `storeVarrayAsTable = "true"` schema annotation causes element collections to be persisted as rows in an index-organized table (IOT), and Oracle Text does not support IOTs.

To be able to use Oracle Text to search the contents of element collections, you must set parameter `genTables="false"` during schema registration, then create the necessary tables manually.

Example 10-49 shows how to manually create a table that corresponds to an element definition in an XML schema. It is identical to Example 3-11 (compare), except that it does not use the clause `ORGANIZATION INDEX OVERFLOW`. The lack of this clause causes the table to be heap-organized instead of index-organized, enabling searching with Oracle Text indexes.

Example 10-49 Creating a Heap-Organized Table that Conforms to an XML Schema

    CREATE TABLE purchaseorder OF XMLType
      XMLSCHEMA "http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd"
      ELEMENT "PurchaseOrder"
      VARRAY "XMLDATA"."actions"."action"
        STORE AS TABLE action_table
                       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
      VARRAY "XMLDATA"."lineitems"."lineitem"
        STORE AS TABLE lineitem_table
                       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)));

### ora:contains Performance

The `ora:contains` XPath function does not depend on a supporting index. `ora:contains` is very flexible. But if you use it to search across large amounts of data without an index, then it can also be resource-intensive. In this section we discuss how to get the best performance from queries that include XPath expressions with `ora:contains`.

Note: Function-based indexes can also be very effective in speeding up XML queries, but they are not generally applicable to Text queries.

The examples in this section use table `PURCHASE_ORDERS_xmltype_big`. This has the same table structure and XML schema as `PURCHASE_ORDERS_xmltype`, but it has around 1,000 rows. Each row has a unique ID (in the "id" column), and some different text in `/purchaseOrder/items/item/comment`. Where an execution plan is shown, it was produced using the SQL*Plus command `AUTOTRACE`. Execution plans can also be produced using SQL commands `TRACE` and `TKPROF`. A description of commands `AUTOTRACE`, `trace` and `tkprof` is outside the scope of this chapter.

This section contains these topics:

- Use a Primary Filter in the Query

- Use a CTXXPATH Index

- XPath Rewrite and the CONTEXT Index

#### Use a Primary Filter in the Query

Because `ora:contains` is relatively expensive to process, Oracle recommends that you write queries that include a primary filter wherever possible. This will minimize the number of rows actually processed by `ora:contains`.

Example 10-50 examines every row in the table (does a full table scan), as we can see from the Plan in Example 10-51. In this example, `ora:contains` is evaluated for every row.

Example 10-50 ora:contains in EXISTSNODE, Large Table

    SELECT id FROM purchase_orders_xmltype_big
      WHERE existsNode(doc,
                       '/purchaseOrder/items/item/comment
                         [ora:contains(text(), "constitution") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

Example 10-51 EXPLAIN PLAN: EXISTSNODE

    Execution Plan
    ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
    1     0   TABLE ACCESS (FULL) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)

If you create an index on the `id` column, as shown in Example 10-52, and add a selective `id` predicate to the query, as shown in Example 10-53, then it is apparent from Example 10-54 that Oracle will drive off the `id` index. `ora:contains` will be executed only for the rows where the `id` predicate is true (where `id` is less than 5).

Example 10-52 B-Tree Index on ID

    CREATE INDEX id_index ON purchase_orders_xmltype_big(id);

This example uses table `purchase_orders`.

Example 10-53 ora:contains in EXISTSNODE, Mixed Query

    SELECT id FROM purchase_orders_xmltype_big
      WHERE existsNode(doc,
                       '/purchaseOrder/items/item/comment
                         [ora:contains(text(), "constitution") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1
        AND id > 5;

Example 10-54 EXPLAIN PLAN: EXISTSNODE

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0    TABLE ACCESS (BY INDEX ROWSELECT ID) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)
       2    1         INDEX (RANGE SCAN) OF 'SELECT ID_INDEX' (INDEX)

#### Use a CTXXPATH Index

The `CTXXPATH` index can be used as a primary filter for `existsNode`. `CTXXPATH` is not related to `ora:contains`. `CTXXPATH` can be a primary filter for any `existsNode` query.

The `CTXXPATH` index stores enough information about a document to produce a superset of the results of an XPath expression. For an `existsNode` query it is often helpful to interrogate the `CTXXPATH` index and then apply `existsNode` to that superset, rather than applying `existsNode` to each document in turn.

Example 10-55 produces the execution plan shown in Example 10-56.

Example 10-55 ora:contains in EXISTSNODE, Large Table

    SELECT id FROM purchase_orders_xmltype_big
      WHERE existsNode(doc,
                       '/purchaseOrder/items/item/comment
                         [ora:contains(text(), "constitution") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

Example 10-56 EXPLAIN PLAN: EXISTSNODE

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0    TABLE ACCESS (FULL) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)

Now create a `CTXXPATH` index on the `DOC` column, as shown in Example 10-57. You can create a `CTXXPATH` index and a `CONTEXT` index on the same column.

Example 10-57 Create a CTXXPATH Index on purchase_orders_xmltype_big(doc)

    CREATE INDEX doc_xpath_index ON purchase_orders_xmltype_big(doc)
      INDEXTYPE IS CTXSYS.CTXXPATH ;

Run Example 10-55 again and you will see from the plan, shown in Example 10-58, that the query now uses the `CTXXPATH` index.

Example 10-58 EXPLAIN PLAN: EXISTSNODE with CTXXPATH Index

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2044)
       1    0     TABLE ACCESS (BY INDEX ROWSELECT ID) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)
                    (Cost=2 Card=1 Bytes=2044)

       2    1          DOMAIN INDEX OF 'DOC_XPATH_INDEX' (INDEX (DOMAIN))
                         (Cost=0)

##### When to Use CTXXPATH

`CTXXPATH` processes only a part of the XPath expression, to give a guaranteed superset (a first-pass estimate) of the results of XPath evaluation.

`CTXXPATH` does not process:

- Functions, including `ora:contains`

- Range operators: `<=`, `<`, `>=`, `>`

- '`..`', "`|`"

- Attribute following '`.`', '`*`' or '`//`'

- Predicate following '`.`' or '`*`'

- '`.`' or '`*`' at the end of a path

- Any node with unabbreviated XPath syntax

So in Example 10-55, the `CTXXPATH` index cannot return results for `/purchaseOrder/items/item/comment[ora:contains(., "constitution")>0]`, because it cannot process the function `ora:contains`. But the `CTXXPATH` index can act as a primary filter by returning all documents that contain the path `/purchaseOrder/items/item/comment`. By calculating this superset of results, `CTXXPATH` can significantly reduce the number of documents considered by `existsNode` in this case.

There are two situations where a `CTXXPATH` index will give a significant performance boost:

- If the document collection is heterogeneous, then knowing which documents contain the path (some `purchaseOrder` with some `items` child with some `item` child with some `comment` child) is enough to significantly reduce the documents considered by `existsNode`.

- If many of the queries include XPath expressions with equality predicates rather than range predicates or functions (such as Example 10-59), then `CTXXPATH` will process those predicates and therefore will be a useful primary filter. `CTXXPATH` handles both string and number equality predicates.

Example 10-59 Equality Predicate in XPath, Big Table

    SELECT count(*) FROM purchase_orders_xmltype_big
      WHERE existsNode(doc,
                       '/purchaseOrder/items/item[USPrice=148.9500]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1;

If you are not sure that `CTXXPATH` will be useful, then create a `CTXXPATH` index and gather statistics on it, as shown in Example 10-60. With these statistics in place, the Oracle Cost Based Optimizer can make an informed choice about whether to use the `CTXXPATH` index or to ignore it.

Example 10-60 Gathering Index Statistics

    BEGIN
      DBMS_STATS.GATHER_INDEX_STATS(OWNNAME  => 'test',
                                    INDNAME  => 'doc_xpath_index');
    END;
    /

This example uses `index-ctxxpath-1`.

##### Maintaining the CTXXPATH Index

The `CTXXPATH` index, like the `CONTEXT` index, is asynchronous. When indexed data is changed, the `CTXXPATH` index might not change until you take some action, such as calling a procedure to synchronize the index. There are a number of ways to manage changes to the `CTXXPATH` index, including some options that are new for this release.

If the `CTXXPATH` index is not kept in synch with the data, then the index gradually becomes less efficient. The `CTXXPATH` index still calculates a superset of the true result, by adding all unsynchronized (unindexed) rows to the result set. So `existsNode` must process all the rows identified by the `CTXXPATH` index plus all unsynchronized (unindexed) rows.

The `CTXXPATH` index may get fragmented over time. A fragmented index uses more space and leads to slower queries. There are a number of ways to optimize (defragment) the `CTXXPATH` index, including some options that are new for this release.

See Also: Oracle Text Reference for information on `CTXXPATH` index maintenance

#### XPath Rewrite and the CONTEXT Index

`ora:contains` does not rely on a supporting index. But under some circumstances an `ora:contains` may use an existing `CONTEXT` index for better performance.

##### Benefits of XPath Rewrite

Oracle will, in some circumstances, rewrite a SQL/XML query into an object-relational query. This is done as part of query optimization and is transparent to the user. Two of the benefits of XPath rewrite are:

- The rewritten query can directly access the underlying object-relational tables instead of processing the whole XML document.

- The rewritten query can make use of any available indexes.

XPath rewrite is a performance optimization. XPath rewrite only makes sense if the XML data is stored object-relationally, which in turn requires the XML data to be XML schema-based.

See Also: "Rewriting of XPath Expressions: XPath Rewrite" for more on the benefits of XPath rewrite Chapter 6, "XPath Rewrite" for a full discussion of XPath rewrite

##### From Documents to Nodes

Consider Example 10-61, a simple `ora:contains` query. To naively process the XPath expression in this query, each cell in the `doc` column must be considered, and each cell must be tested to see if it matches this XPath expression:

    /purchaseOrder/items/item/comment[ora:contains(text(), "electric")>0]

Example 10-61 ora:contains in existsNode

    SELECT id FROM purchase_orders_xmltype
      WHERE existsNode(doc,
                       '/purchaseOrder/items/item/comment
                         [ora:contains(text(), "electric") > 0]',
                       'xmlns:ora="http://xmlns.oracle.com/xdb"')
            = 1 ;

This example uses table `purchase_orders_xmltype`.

But if `doc` is schema-based, and the `purchaseOrder` documents are physically stored in object-relational tables, then it makes sense to go straight to column `/purchaseOrder/items/item/comment` (if such a column exists) and test each cell there to see if it matches "`electric`".

This is the first XPath-rewrite step. If the first argument to `ora:contains` (`text_input`) maps to a single relational column, then `ora:contains` executes against that column. Even if there are no indexes involved, this can significantly improve query performance.

##### From ora:contains to contains

As noted in "From Documents to Nodes", Oracle may rewrite a query so that an XPath expression in `existsNode` may be resolved by applying `ora:contains` to some underlying column instead of applying the whole XPath to the whole XML document. In this section it will be shown how that query might make use of a `CONTEXT` index on the underlying column.

If you are running `ora:contains` against a text node or attribute that maps to a column with a `CONTEXT` index on it, why would you not use that index? One powerful reason is that a rewritten query should give the same results as the original query. To ensure consistent results, the following conditions must be true before a `CONTEXT` index can be used.

First, the `ora:contains` target (`input_text`) must be either a single text node whose parent node maps to a column or an attribute that maps to a column. The column must be a single relational column (possibly in a nested table).

Second, as noted in "Policies for ora:contains Queries", the indexing choices (for `contains`) and policy choices (for `ora:contains`) affect the semantics of queries. A simple mismatch might be that the index-based `contains` would do a case-sensitive search, while `ora:contains` specifies a case-insensitive search. To ensure that the `ora:contains` and the rewritten `contains` have the same semantics, the `ora:contains` policy must exactly match the index choices of the `CONTEXT` index.

Both the `ora:contains` policy and the `CONTEXT` index must also use the `NULL_SECTION_GROUP` section group type. The default section group for an `ora:contains` policy is `ctxsys.NULL_SECTION_GROUP`.

Third, the `CONTEXT` index is generally asynchronous. If you add a new document that contains the word "`dog`", but do not synchronize the `CONTEXT` index, then a `contains` query for "`dog`" will not return that document. But an `ora:contains` query against the same data will. To ensure that the `ora:contains` and the rewritten `contains` will always return the same results, the `CONTEXT` index must be built with the `TRANSACTIONAL` keyword in the `PARAMETERS` string.

See Also: Oracle Text Reference

##### XPath Rewrite: Summary

A query with `existsNode`, `extract` or `extractValue`, where the XPath includes `ora:contains`, may be considered for XPath rewrite if:

- The XML is schema-based

- The first argument to `ora:contains` (`text_input`) is either a single text node whose parent node maps to a column, or an attribute that maps to a column. The column must be a single relational column (possibly in a nested table).

The rewritten query will use a `CONTEXT` index if:

- There is a `CONTEXT` index on the column that the parent node (or attribute node) of `text_input` maps to.

- The `ora:contains` policy exactly matches the index choices of the `CONTEXT` index.

- The `CONTEXT` index was built with the `TRANSACTIONAL` keyword in the `PARAMETERS` string.

XPath rewrite can speed up queries significantly, especially if there is a suitable `CONTEXT` index.

## Text Path BNF Specification

    HasPathArg           ::=    LocationPath
                             |  EqualityExpr
    InPathArg            ::=    LocationPath
    LocationPath         ::=    RelativeLocationPath
                             |  AbsoluteLocationPath
    AbsoluteLocationPath ::=    ("/" RelativeLocationPath)
                             |  ("//" RelativeLocationPath)
    RelativeLocationPath ::=    Step
                             |  (RelativeLocationPath "/" Step)
                             |  (RelativeLocationPath "//" Step)
    Step                 ::=    ("@" NCName)
                             |  NCName
                             |  (NCName Predicate)
                             |  Dot
                             |  "*"
    Predicate            ::=    ("[" OrExp "]")
                             |  ("[" Digit+ "]")
    OrExpr               ::=    AndExpr
                             |  (OrExpr "or" AndExpr)
    AndExpr              ::=    BooleanExpr
                             |  (AndExpr "and" BooleanExpr)
    BooleanExpr          ::=    RelativeLocationPath
                             |  EqualityExpr
                             |  ("(" OrExpr ")")
                             |  ("not" "(" OrExpr ")")
    EqualityExpr         ::=    (RelativeLocationPath "=" Literal)
                             |  (Literal "=" RelativeLocationPath)
                             |  (RelativeLocationPath "=" Literal)
                             |  (Literal "!=" RelativeLocationPath)
                             |  (RelativeLocationPath "=" Literal)
                             |  (Literal "!=" RelativeLocationPath)
    Literal              ::=    (DoubleQuote [~"]* DoubleQuote)
                             |  (SingleQuote [~']* SingleQuote)
    NCName               ::=    (Letter |  Underscore) NCNameChar*
    NCNameChar           ::=    Letter
                             |  Digit
                             |  Dot
                             |  Dash
                             |  Underscore
    Letter               ::=    ([a-z] | [A-Z])
    Digit                ::=    [0-9]
    Dot                  ::=    "."
    Dash                 ::=    "-"
    Underscore           ::=    "_"

## Support for Full-Text XML Examples

This section contains these topics:

- Purchase-Order XML Document, po001.xml

- CREATE TABLE Statements

- Purchase-Order XML Schema for Full-Text Search Examples

### Purchase-Order XML Document, po001.xml

Example 10-62 Purchase Order XML Document, po001.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                   orderDate="1999-10-20">
      <shipTo country="US">
        <name>Alice Smith</name>
        <street>123 Maple Street</street>
        <city>Mill Valley</city>
        <state>CA</state>
        <zip>90952</zip>
      </shipTo>
      <billTo country="US">
        <name>Robert Smith</name>
        <street>8 Oak Avenue</street>
        <city>Old Town</city>
        <state>PA</state>
        <zip>95819</zip>
      </billTo>
      <comment>Hurry, my lawn is going wild!</comment>
      <items>
        <item partNum="872-AA">
          <productName>Lawnmower</productName>
          <quantity>1</quantity>
          <USPrice>148.95</USPrice>
          <comment>Confirm this is electric</comment>
        </item>
        <item partNum="926-AA">
          <productName>Baby Monitor</productName>
          <quantity>1</quantity>
          <USPrice>39.98</USPrice>
          <shipDate>1999-05-21</shipDate>
        </item>
      </items>
    </purchaseOrder>

### CREATE TABLE Statements

Example 10-63 CREATE TABLE purchase_orders

    CREATE TABLE purchase_orders (id   NUMBER,
                                  doc  VARCHAR2(4000));
    INSERT INTO purchase_orders (id, doc)
      VALUES (1,
              '<?xml version="1.0" encoding="UTF-8"?>
               <purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                              xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                              orderDate="1999-10-20">
                 <shipTo country="US">
                   <name>Alice Smith</name>
                   <street>123 Maple Street</street>
                   <city>Mill Valley</city>
                   <state>CA</state>
                   <zip>90952</zip>
                 </shipTo>
                 <billTo country="US">
                   <name>Robert Smith</name>
                   <street>8 Oak Avenue</street>
                   <city>Old Town</city>
                   <state>PA</state>
                   <zip>95819</zip>
                 </billTo>
                 <comment>Hurry, my lawn is going wild!</comment>
                 <items>
                   <item partNum="872-AA">
                     <productName>Lawnmower</productName>
                     <quantity>1</quantity>
                     <USPrice>148.95</USPrice>
                     <comment>Confirm this is electric</comment>
                   </item>
                   <item partNum="926-AA">
                     <productName>Baby Monitor</productName>
                     <quantity>1</quantity>
                     <USPrice>39.98</USPrice>
                     <shipDate>1999-05-21</shipDate>
                   </item>
                 </items>           </purchaseOrder>');COMMIT;

Example 10-64 CREATE TABLE purchase_orders_xmltype

    CREATE TABLE purchase_orders_xmltype (id  NUMBER ,
                                          doc XMLType);
    INSERT INTO purchase_orders_xmltype (id, doc)
      VALUES (1,
              XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
                         <purchaseOrder
                           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xsi:noNamespaceSchemaLocation="po.xsd"
                           orderDate="1999-10-20">
                           <shipTo country="US">
                             <name>Alice Smith</name>
                             <street>123 Maple Street</street>
                             <city>Mill Valley</city>
                             <state>CA</state>
                             <zip>90952</zip>
                           </shipTo>
                           <billTo country="US">
                             <name>Robert Smith</name>
                             <street>8 Oak Avenue</street>
                             <city>Old Town</city>
                             <state>PA</state>
                             <zip>95819</zip>
                           </billTo>
                           <comment>Hurry, my lawn is going wild!</comment>
                           <items>
                             <item partNum="872-AA">
                               <productName>Lawnmower</productName>
                               <quantity>1</quantity>
                               <USPrice>148.95</USPrice>
                               <comment>Confirm this is electric</comment>
                             </item>
                             <item partNum="926-AA">
                               <productName>Baby Monitor</productName>
                               <quantity>1</quantity>
                               <USPrice>39.98</USPrice>
                               <shipDate>1999-05-21</shipDate>
                             </item>
                           </items>
                       </purchaseOrder>'));
    COMMIT;

Example 10-65 CREATE TABLE purchase_orders_xmltype_table

    CREATE TABLE purchase_orders_xmltype_table OF XMLType;

    INSERT INTO purchase_orders_xmltype_table
      VALUES (
        XMLType ('<?xml version="1.0" encoding="UTF-8"?>              <purchaseOrder
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                    orderDate="1999-10-20">
                    <shipTo country="US">
                      <name>Alice Smith</name>
                      <street>123 Maple Street</street>
                      <city>Mill Valley</city>
                      <state>CA</state>
                      <zip>90952</zip>
                    </shipTo>
                    <billTo country="US">
                      <name>Robert Smith</name>
                      <street>8 Oak Avenue</street>
                      <city>Old Town</city>
                      <state>PA</state>
                      <zip>95819</zip>
                    </billTo>
                    <comment>Hurry, my lawn is going wild!</comment>
                    <items>
                      <item partNum="872-AA">
                        <productName>Lawnmower</productName>
                        <quantity>1</quantity>
                        <USPrice>148.95</USPrice>
                        <comment>Confirm this is electric</comment>
                      </item>
                      <item partNum="926-AA">
                        <productName>Baby Monitor</productName>
                        <quantity>1</quantity>
                        <USPrice>39.98</USPrice>
                        <shipDate>1999-05-21</shipDate>
                      </item>
                    </items>
                  </purchaseOrder>'));
    COMMIT;

### Purchase-Order XML Schema for Full-Text Search Examples

Example 10-66 Purchase-Order XML Schema for Full-Text Search Examples

    <?xml version="1.0" encoding="UTF-8"?>
    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <xsd:annotation>
        <xsd:documentation xml:lang="en">
          Purchase order schema for Example.com.
          Copyright 2000 Example.com. All rights reserved.
        </xsd:documentation>
      </xsd:annotation>
      <xsd:element name="purchaseOrder" type="PurchaseOrderType"/>
      <xsd:element name="comment" type="xsd:string"/>
      <xsd:complexType name="PurchaseOrderType">
        <xsd:sequence>
          <xsd:element name="shipTo" type="USAddress"/>
          <xsd:element name="billTo" type="USAddress"/>
          <xsd:element ref="comment" minOccurs="0"/>
          <xsd:element name="items" type="Items"/>
        </xsd:sequence>
        <xsd:attribute name="orderDate" type="xsd:date"/>
      </xsd:complexType>
      <xsd:complexType name="USAddress">
        <xsd:sequence>
          <xsd:element name="name" type="xsd:string"/>
          <xsd:element name="street" type="xsd:string"/>
          <xsd:element name="city" type="xsd:string"/>
          <xsd:element name="state" type="xsd:string"/>
          <xsd:element name="zip" type="xsd:decimal"/>
        </xsd:sequence>
        <xsd:attribute name="country" type="xsd:NMTOKEN" fixed="US"/>
      </xsd:complexType>
      <xsd:complexType name="Items">
        <xsd:sequence>
          <xsd:element name="item" minOccurs="0" maxOccurs="unbounded">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="productName" type="xsd:string"/>
                <xsd:element name="quantity">
                  <xsd:simpleType>
                    <xsd:restriction base="xsd:positiveInteger">
                      <xsd:maxExclusive value="100"/>
                    </xsd:restriction>
                  </xsd:simpleType>
                </xsd:element>
                <xsd:element name="USPrice" type="xsd:decimal"/>
                <xsd:element ref="comment" minOccurs="0"/>
                <xsd:element name="shipDate" type="xsd:date" minOccurs="0"/>
              </xsd:sequence>
              <xsd:attribute name="partNum" type="SKU" use="required"/>
            </xsd:complexType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
      <!-- Stock Keeping Unit, a code for identifying products -->
      <xsd:simpleType name="SKU">
        <xsd:restriction base="xsd:string">
          <xsd:pattern value="\d{3}-[A-Z]{2}"/>
        </xsd:restriction>
      </xsd:simpleType>
    </xsd:schema>